package com.carlyang.studentinfo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.carlyang.studentinfo.model.Student;
import com.carlyang.studentinfo.model.Class;
import com.carlyang.studentinfo.model.PageBean;
import com.carlyang.studentinfo.util.DateUtil;
import com.carlyang.studentinfo.util.LogUtil;
import com.carlyang.studentinfo.util.StringUtil;

public class StudentDao {
	public static final String TAG = "StudentDao";
	
	public ResultSet getStudentList(Connection con,PageBean pageBean,Student student,String bbirthday,String ebirthday)throws Exception{
		ResultSet resultSet = null;
		//使用关联查询，需要关联班级
		//select * from t_student s,t_class c where s.classId=c.id limit start,num
		//SELECT * FROM t_student s,t_class c WHERE s.classId=c.id AND TO_DAYS(s.birthday)>=TO_DAYS('1991-03-01') ORDER BY s.id_stu LIMIT 0,20;
		StringBuilder msq = new StringBuilder("select * from t_student s,t_class c where c.id=s.classId");
		if (StringUtil.isNotEmpty(student.getStuNo())) {
			msq.append(" and s.stuNo like '%"+student.getStuNo()+"%'");
		}
		if (StringUtil.isNotEmpty(student.getStuName())) {
			msq.append(" and s.stuName like '%"+student.getStuName()+"%'");
		}
		if (StringUtil.isNotEmpty(student.getSex())) {
			msq.append(" and s.sex='"+student.getSex()+"'");
		}
		if (StringUtil.isNotEmpty(bbirthday)) {
			msq.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+student.getBirthday()+"')");
		}
		if (StringUtil.isNotEmpty(ebirthday)) {
			msq.append(" and TO_DAYS(s.eirthday)<=TO_DAYS('"+student.getBirthday()+"')");
		}
		if (student.getClassId()!=-1) {
			msq.append(" and s.classId='"+student.getClassId()+"'");
		}
		
		msq.append(" order by s.id_stu");
		if (pageBean!=null) {
			msq.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
		}
		LogUtil.i(TAG, "getStudentList SQL语句:"+msq.toString());
		PreparedStatement pstmt = con.prepareStatement(msq.toString());
		resultSet = pstmt.executeQuery();
		return resultSet;
	}
	
	public int getStudentCount(Connection con,Student student,String bbirthday,String ebirthday) throws Exception{
		StringBuilder msq = new StringBuilder("select count(*) as total from t_student s,t_class c where s.classId=c.id");
		if (StringUtil.isNotEmpty(student.getStuNo())) {
			msq.append(" and s.stuNo like '%"+student.getStuNo()+"%'");
		}
		if (StringUtil.isNotEmpty(student.getStuName())) {
			msq.append(" and s.stuName like '%"+student.getStuName()+"%'");
		}
		if (StringUtil.isNotEmpty(student.getSex())) {
			msq.append(" and s.sex='"+student.getSex()+"'");
		}
		if (StringUtil.isNotEmpty(bbirthday)) {
			msq.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+student.getBirthday()+"')");
		}
		if (StringUtil.isNotEmpty(ebirthday)) {
			msq.append(" and TO_DAYS(s.eirthday)<=TO_DAYS('"+student.getBirthday()+"')");
		}
		if (student.getClassId()!=-1) {
			msq.append(" and s.classId='"+student.getClassId()+"'");
		}
		LogUtil.i(TAG, "getStudentCount SQL语句:"+msq.toString());
		PreparedStatement pstmt = con.prepareStatement(msq.toString());
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt("total");
		}else {
			return 0;
		}
	}
	
	/**
	 * delete from tableName where field in (1,3,5)
	 * @param con
	 * @param delIds
	 * @return
	 */
	public int deleteStudent(Connection con,String delIds) throws Exception{
		String sql="delete from t_student where id_stu in ("+delIds+")";
		LogUtil.i(TAG, "deleteStudent SQL语句:"+sql);
		PreparedStatement pstmt = con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}
	
	public int addStudent(Connection con,Student student) throws Exception{
		String sql = "insert into t_student values(null,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, student.getStuNo());
		pstmt.setString(2, student.getStuName());
		pstmt.setString(3, student.getSex());
		pstmt.setString(4, DateUtil.formatDate(student.getBirthday(), "yyyy-MM-dd"));
		pstmt.setInt(5, student.getClassId());
		pstmt.setString(6, student.getEmail());
		pstmt.setString(7, student.getStuDesc());
		LogUtil.i(TAG, "addStudent SQL语句:"+pstmt.toString());
		return pstmt.executeUpdate();
	}
	
	/**
	 * 修改更新学生信息，在web页中会填所有项的数据，所有都要修改
	 * @param con
	 * @param student
	 * @return
	 * @throws Exception
	 */
	public int updateStudent(Connection con,Student student) throws Exception{
		String sql = "update t_student set stuNo=?,stuName=?,sex=?,birthday=?,classId=?,email=?,stuDesc=? where id_stu=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, student.getStuNo());
		pstmt.setString(2, student.getStuName());
		pstmt.setString(3, student.getSex());
		pstmt.setString(4, DateUtil.formatDate(student.getBirthday(), "yyyy-MM-dd"));
		pstmt.setInt(5, student.getClassId());
		pstmt.setString(6, student.getEmail());
		pstmt.setString(7, student.getStuDesc());
		pstmt.setInt(8, student.getId());
		LogUtil.i(TAG, "updateStudent SQL语句:"+pstmt.toString());
		return pstmt.executeUpdate();
	}
}
